<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Attendance_model extends CI_Model
{
	
	public function __construct()
	{
		parent::__construct();
	}
	
	
	/**
	* @package: Attendance details 
	* @author: pratik
*/

public function add_absent($where)
{
	if($where['month_id'] == date('m')){
	$date_id = date('d');	
	}else{
		$date_id = cal_days_in_month(CAL_GREGORIAN, $where['month_id'], '20'.$where['year_id']) + 1;
	}	
	for($i=1;$i<$date_id;$i++){
		if($i < 10){
		$day = 'day_0'.$i;		
		}else{
			$day = 'day_'.$i;	
		}	
	$this->db->select("{$day}");
	$this->db->from('daily_attendance_sheet');
	$this->db->where($where);
	$this->db->where("{$day} != 'sunday'");
	$this->db->where("{$day} != 'paid_leave'");
	$this->db->where("{$day} != 'unpaid_leave'");
	$this->db->where("{$day} = ''");	
	$no_attendance = $this->db->get()->row();	
	foreach($no_attendance as $key=>$value){
		$this->db->update('daily_attendance_sheet',array($key=>'absent'),$where);
		}	
	}	
}

public function get_absent($where){
	/*
	$this->db->select('*');
	$this->db->from('daily_attendance_sheet');
	$this->db->where($where);
	*/
	//$wher
	//$this->db->get_where('daily_attendance_sheet',$where);
}

public function get_all_attendance($year_id,$month_id){  
	$this->db->select('daily_attendance_sheet.*,users.first_name');
	$this->db->from('daily_attendance_sheet');
	$this->db->where('year_id',$year_id);
	$this->db->where('month_id',$month_id);
	$this->db->join('users','daily_attendance_sheet.user_id = users.id');
	return $this->db->get()->result();	
  }
  
public function get_attendance_months(){
		$this->db->distinct('month_id');		
		$this->db->select('month_id,year_id');		
		$this->db->from('daily_attendance_sheet');
		//$this->db->get();
		return $this->db->get()->result();
		//return $this->db->last_query();
	}

public function check_edit_attendance($user_id,$year_id,$month_id){
		$where = array(
		'user_id' => $user_id,
		'year_id' => $year_id,
		'month_id' => $month_id,
		);
		$this->db->select('attendance_timings.date_id');
		$this->db->from('attendance_timings');
		$this->db->where($where);
		$this->db->where('(entry_flag = 1 OR exit_flag = 1)');
		return $this->db->get()->result();
		//$this->db->get();
		//return $this->db->last_query();
	}
 
  public function get_attendance_details()
  {	  
	  $user_id = array(
	  'user_id' => $this->session->userdata('user_id')
	  );	  
 	return $query = $this->db->get_where('daily_attendance_sheet', $user_id)->result();		
  }
	public function get_details($year_id,$month_id)
	{
		$serach = array(
		'year_id' =>$year_id,
		'month_id'=>$month_id,
		'user_id' => $this->session->userdata('user_id')		
		);
		
		return $this->db->get_where('daily_attendance_sheet',$serach)->result();
	}
	
	public function get_total_working_hr()
	{
	//	$this->db->select_avg('age');
		//$query = $this->db->get('members');		
	}
	
	public function get_time($year_id,$month_id,$date_id){
		
		$check = array('year_id' => $year_id, 'month_id' => $month_id, 'date_id' => $date_id,'user_id'=>$this->session->userdata('user_id'));
		$this->db->select('entry_time, exit_time,entry_flag,exit_flag,lunch_in_time, lunch_out_time,lunch_in_flag,lunch_out_flag');
		$this->db->from('attendance_timings');
		$this->db->where($check);
		return $query = $this->db->get()->row_array();
	}
	
	
	public function get_working_hr()
	{
		$check = array('user_id'=>$this->session->userdata('user_id'));	
		$this->db->select('min_working_hour');
		$this->db->from('salary_account');
		$this->db->where($check);
		$query = $this->db->get()->row();
		 if($query){
			return $query->min_working_hour;
		}else{
			return 0;
		}
	}
	
	/*function present($month_id,$year_id){
		$this->db->select();
	}*/
	
	function get_time_by_id($search){
		return $this->db->get_where('attendance_timings',$search)->row_array();
	}
	
	public function get_exit_time($search){
		//return $this->db->get_where('attendance_timings',$search)->row_array();
	}
	
	public function get_date_col_name()
	{
		return $this->db->list_fields('daily_attendance_sheet');;
	}
	
	function get_server_time()
	{
		$query = $this->db->query("select CURTIME()")->row_array();
		return $query['CURTIME()'];		
	}
	
	function get_server_date()
	{
		$query = $this->db->query("select CURDATE()")->row_array();
		return $query['CURDATE()'];		
	}
	
	function insert_entry_time($insert)
	{
		//echo "insert"; exit;
		$query = $this->db->get_where('attendance_timings', array(
		'year_id'=>$insert['year_id'],
		'month_id'=>$insert['month_id'],
		'date_id'=>$insert['date_id'],
		'user_id'=>$insert['user_id']
		));
 if($query->num_rows() == 0)
 { 
 $query = $this->db->insert('attendance_timings', $insert);
		if($query)
		{			
		return "Inserted Successfully";	
		}
		else {
		return "Error in database. Please try Again";
		}
 }
 else {	return 0;  } 
	}
		
	/*fucntion to check entry time */
	public function check_entry_time($check)
	{
		$query = $this->db->get_where('attendance_timings', array(
		'month_id'=>$check['month_id'],
		'date_id'=>$check['date_id'],
		'user_id'=>$check['user_id']
		));
 if($query->num_rows() > 0){
	 $row = $query->row_array();
	 return $row['entry_time'];
}
else{return false;}
}

public function check_entry_id($check)
	{
		$query = $this->db->get_where('attendance_timings', array(
		'month_id'=>$check['month_id'],
		'date_id'=>$check['date_id'],
		'user_id'=>$check['user_id']
		));
 if($query->num_rows() > 0){
	return  $row = $query->row_array();	 
}
else{return false;}
}

public function insert_exit_time($time)
	{
	$check = array(
		 'month_id' => $time['month_id'],
		 'date_id' => $time['date_id'],
		 'user_id' => $time['user_id'],
			);
				 
	$data = array(
               'exit_time' => $time['exit_time'],
			   'exit_ip'=>$_SERVER['REMOTE_ADDR']            
            );
			
$query = $this->db->update('attendance_timings', $data, $check);	 
if($query)	{	
	// after exit time entered update the daily attendance sheets	
return "Attendance Updated Successfully";	
			}
else	{
return false;
		}
  }
  
 public function check_exit_time($check)
  {
	 $query = $this->db->get_where('attendance_timings', $check,1); 
	 if($query->num_rows() == 1)
	 {
		 $row = $query->row_array();
		 return $row['exit_time'];
	 }
	 else {
		return false; 
	 }
  }
 
 
  /**
* 
* new function attendance 
* @author pratik
* 
*/

public function get_daily_attendance()
{
	
}

   public function insert_daily_attendance_sheet($insert,$sunday)
  {
	  $check = array(
	  'year_id'  =>$insert['year_id'],
	  'month_id' =>	$insert['month_id'],
	  'user_id'  =>$insert['user_id'],	  
	  );
	
	 foreach($sunday as $sundays)
	 {	
		$insert_sunday['day_'.$sundays] = 'sunday';		
	 }
	
	$data = array(
	 array_pop(array_keys($insert))  => end(array_values($insert))
	 );	
	 
	// print_r($data);exit;'
	  $check_query = $this->db->get_where('daily_attendance_sheet', $check); 
	 if($check_query->num_rows() == 1)
	  {
	$query = $this->db->update('daily_attendance_sheet', $data, $check);	
	  }
	  else {	  
		$query = $this->db->insert('daily_attendance_sheet', $insert);    
	 $id = array(
	 'id' => $this->db->insert_id()
	 );
	 	 
	 //inserting all sundays
	 $this->db->update('daily_attendance_sheet', $insert_sunday, $id);	 
	 
	 //inserting all holidays
	 //$this->db->update('daily_attendance_sheet', $insert_holiday, $id);	 
	   
	   //insert into salary table
	   
	 //  $salary = $this->get_salary_by_id();		
		
	  //insert into monthly table
		/*$salary_insert= array(
		'year_id'=> $insert['year_id'],
		'month_id'=>$insert['month_id'],
		'user_id'=>$insert['user_id'],
		'salary_id' => $salary['id'],
		);*/
		
		
	  //$query = $this->db->insert('monthly_salary_details', $salary_insert);  
	  }
		/*if($query)
		{			
		return "Inserted Successfully";	
		}
		else {
		return "Error in database. Please try Again";
		}*/
  }
  
  public function get_salary_by_id($id = null)
  {
	 $user_id = isset($id) ?  $id : $this->session->userdata('user_id');
	  $id = array(
	  'user_id' => $user_id
	  );
	return  $query = $this->db->get_where('salary_account',$id)->row_array();	 
  }
  
  public function add_to_monthly_salary()
  {
	
  }
  
  /**
  * update
  */
 
 public function insert_edited_time($where,$entry_time)
 {
	 $insert = array(
	 	'year_id'   => $where['year_id'],
	 	'month_id'  => $where['month_id'],
	 	'date_id'   => $where['date_id'],
	 	'user_id'   => $where['user_id'],
	 	'entry_time'=> $entry_time,
	 	'entry_ip'  => $_SERVER['REMOTE_ADDR'],
		'entry_flag'=> 1
	);
	$this->db->insert('attendance_timings',$insert); 	
 }
 
public function update_entry_time($where,$entry_time)
 {
 	$this->db->update('attendance_timings',array('entry_time'=>$entry_time,'entry_flag'=>1),$where);	
 }
 
public function update_lunch_in_time($where,$lunch_in_time)
 {
 	$lunch=$lunch_in_time;
 	if(empty($lunch))
	{
 	$this->db->update('attendance_timings',array('lunch_in_time'=>NULL,'lunch_in_flag'=>1),$where);
	}else{
	$this->db->update('attendance_timings',array('lunch_in_time'=>$lunch_in_time,'lunch_in_flag'=>1),$where);	
	}	
 }
 
public function update_lunch_out_time($where,$lunch_out_time)
 {
 	$lunch_out=$lunch_out_time;
	if(empty($lunch_out)){
 	$this->db->update('attendance_timings',array('lunch_out_time'=>NULL,'lunch_out_flag'=>1),$where);	
	}else{
	$this->db->update('attendance_timings',array('lunch_out_time'=>$lunch_out_time,'lunch_out_flag'=>1),$where);
	}
 }
  
public function update_exit_time($where,$exit_time)
 {
 	$this->db->update('attendance_timings',array('exit_time'=>$exit_time,'exit_flag'=>1),$where);	
 }
 
public function update_working_hr($src,$hrs)
 { 	
	$new_search = array(
	'year_id' => $src['year_id'],
	'month_id' => $src['month_id'],
	'user_id' => $src['user_id'],
	);
//print_r($hrs);
 		//exit;
	$date_col = 'day_'.$src['date_id'];
 	$this->db->update('daily_attendance_sheet',array($date_col=>$hrs),$new_search);
 }
 
 public function insert_lunch_in_time($time)
	{
	$check = array(
		 'month_id' => $time['month_id'],
		 'date_id' => $time['date_id'],
		 'user_id' => $time['user_id'],
			);
				 
	$data = array(
               'lunch_in_time' 	=> 	$time['lunch_in_time'],
			   'lunch_in_ip'	=>	$_SERVER['REMOTE_ADDR']            
            );
			
			
$query = $this->db->update('attendance_timings', $data, $check);	 
if($query)	{	
	// after exit time entered update the daily attendance sheets	
return "Attendance Updated Successfully";	
			}
else	{
return false;
		}
  }
 
 public function check_lunch_in_time($check)
  {
	 $query = $this->db->get_where('attendance_timings', $check,1); 
	 if($query->num_rows() == 1)
	 {
		 $row = $query->row_array();
		 return $row['lunch_in_time'];
	 }
	 else {
		return false; 
	 }
  }
  
  
  public function insert_lunch_out_time($time)
	{
	$check = array(
		 'month_id' => $time['month_id'],
		 'date_id' => $time['date_id'],
		 'user_id' => $time['user_id'],
			);
				 
	$data = array(
               'lunch_out_time' => $time['lunch_out_time'],
			   'lunch_out_ip'=>$_SERVER['REMOTE_ADDR']            
            );
			
$query = $this->db->update('attendance_timings', $data, $check);	 
if($query)	{	
	// after exit time entered update the daily attendance sheets	
return "Attendance Updated Successfully";	
			}
else	{
return false;
		}
  }
 
 public function check_lunch_out_time($check)
  {
	 $query = $this->db->get_where('attendance_timings', $check,1); 
	 if($query->num_rows() == 1)
	 {
		 $row = $query->row_array();
		 return $row['lunch_out_time'];
	 }
	 else {
		return false; 
	 }
  }
   public function get_settings(){
   	$this->db->select('value');
	$this->db->from('settings');
	$this->db->where('name','worksheet_calculation');
	return $this->db->get()->row()->value;
	$this->db->close();
   }
	
	
	/*get the salary slip if generated*/
	public function get_salary_slip($year_id,$month_id){
		$this->db->select();		
	  }
	 
	  
	  /**
	  * @package:attendance worksheet 
	  * worksheet functionality starts here
	  * w = worksheet
	  * @author: pratik <pratik@fedobe.org>
	  */
	  
	  
	public function worksheet_status($user_id,$month_id,$year_id)
	{
		
		/*getting the present days*/
		$half_day = 0;$unpaid = 0;$absent = 0;

		$present  = $this->present_array($user_id,$month_id,$year_id);	
//print_r($present);
	
		foreach($present as $p)
		{
			$date_id = substr($p,4);
			$this->db->select('*');
			$this->db->from('worksheet_verify');
			$this->db->where('year_id',$year_id);
			$this->db->where('month_id',$month_id);
			$this->db->where('date_id',$date_id);
			$this->db->where('user_id',$user_id);
			$q  = $this->db->get()->row();
			
			if($q)
			{		
				if( (strtotime($q->total_worktime)) <= (strtotime('04:00:00')) AND  (strtotime($q->total_worktime)) > (strtotime('02:00:00')) )
				{
					
					$half_day += 1;					
				}elseif(strtotime($q->total_worktime) <= strtotime('02:00:00') AND  (strtotime($q->total_worktime)) > (strtotime('00:00:00')) ){
				$unpaid +=1;					
				}elseif((strtotime($q->total_worktime)) == (strtotime('00:00:00'))){
					$absent +=1;
				}
				
			}else{
				$absent +=1;
			}
		}
		$return = array(
		'half_day' => $half_day,
		'unpaid'=> $unpaid,
		'absent'=> $absent,
		);
		return $return;
	}
		
	  
	   public function present_array($user_id,$month_id,$year_id){
	  	
		//$this->db->select('*');
		$this->db->from('daily_attendance_sheet');
		$this->db->where('user_id',$user_id);
		$this->db->where('year_id',$year_id);
		$this->db->where('month_id',$month_id);
		$result = $this->db->get()->row_array();		
		$check = array('absent','holiday','sunday','rejected','unpaid_leave','','paid_leave');
		$check_key = array('id','year_id','month_id','user_id');
		$present_days = array();
		foreach($result as $key=>$value){			
				if(!in_array($value,$check) AND !in_array($key,$check_key)){				
				$present_days[] = $key;
			}
		}		
		return $present_days;
		$this->db->close();
	 }
	  
public function fine($where){		
		$late=0;$days_fine=NULL;	
		$this->db->select('entry_time');
		$this->db->from('attendance_timings');
		$this->db->where($where);
		$present = $this->db->get()->result_array();
		
		foreach($present as $key=>$value){
			
			foreach($value as $val){
				$entry=strtotime($val);
				$to_be_time=strtotime('10:00:59');
				
				if($entry>$to_be_time){
					$late +=1;
					$fine = $late*50;
					$days_fine = array('days'=>$late,'amount'=>$fine,'fine_charges'=>"50");
				}
			}
		}
		return $days_fine;
	}
	
	public function check_ip_date($user_id,$date_id,$month_id,$year_id){
		$where = array(
		'user_id' 	=> $user_id,
		'year_id' 	=> $year_id,
		'month_id' 	=> $month_id,
		'date_id' 	=> $date_id,
		);
		$this->db->select('entry_ip,lunch_in_ip,lunch_out_ip,exit_ip');
		$this->db->from('attendance_timings');
		$this->db->where($where);
		return $this->db->get()->result();
		
	}
		
	public function check_ip($ip){		
		$query  = $this->db->query("SELECT * FROM `ip_table` WHERE ".$ip." BETWEEN `from_ip` AND `to_ip`");		
		$result	= $query->num_rows();		
		if($result > 0){						
			return TRUE;
		}else{			
			return FALSE;
		}
	}
	public function get_worksheet_year()
	{
		$this->db->distinct('year_id');
		$this->db->select('year_id');
		$this->db->order_by("year_id","desc");		
		//$this->db->where('user_id', $this->session->userdata('user_id'));
		$query = $this->db->get('attendance_timings')->result();
		return $query;
	}
	
	public function get_worksheet_months()
	{
		$this->db->distinct('month_id');
		$this->db->select('month_id,year_id');
		$this->db->order_by("month_id","desc");
		//$this->db->where('user_id', $this->session->userdata('user_id'));
		return $query = $this->db->get('attendance_timings')->result();
	}
	// end of function	  
	  
}